﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using System.Data.SqlClient;
using System.Collections;
namespace RLZY.others
{
    class NotKey
    {
        /// <summary>
        /// 根据部门获得非关键项表
        /// </summary>
        public DataSet GetListNotKey(string 部门单位, String 车间班组)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");

            strSql.Append(" [ID], [日期], [部门单位], [车间班组], [用工性质], [姓名],  [粮贴], [洗理费], [水电气补贴], [三项补贴], [回贴],  [职称津贴], [特岗津贴], [行车工津贴], [卫生纸费],   [扣电视费], [扣治安费], [扣会费], [扣互助金], [扣丧葬费],  [岗位名称], [性别], [参加工作时间], [工号], [身份证号], [帐号], [老工号], [备注],部门单位备注,  [公积金号], [医疗保险号], [出生日期], [党政职别], [职称], [干工标识], [籍贯_出生地], [毕业时间文化程度] ");
            strSql.Append(" FROM GZ ");
            strSql.Append("  where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') and (车间班组 like '%'+@strWhere1+'%' or @strWhere1='') ");
            strSql.Append(" and  是否保护='否'  order by  姓名 asc ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",部门单位),
                new SqlParameter("@strWhere1",车间班组),
                

            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
        /// <summary>
        /// 身份证存在
        /// </summary>
        public SqlDataReader GetListNameId(string 身份证)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");

            strSql.Append(" 身份证号 from GZ  where  身份证号=@身份证号");
            
            
            strSql.Append(" and  是否保护='否' ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@身份证号",身份证),
               
            };

            return DbHelperSQL.ExecuteReader(strSql.ToString(), param);

        }
        /// <summary>
        /// 银行账号存在
        /// </summary>
        public SqlDataReader GetListZH(string 账号)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");

            strSql.Append(" 帐号 from GZ  where  帐号=@账号");


            strSql.Append(" and  是否保护='否' ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@账号",账号),
               
            };

            return DbHelperSQL.ExecuteReader(strSql.ToString(), param);

        }
        /// <summary>
        /// 根据部门获得非关键项表
        /// </summary>
        public DataSet searchNotKey(string 部门单位, String 车间班组,String 姓名)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");

            strSql.Append(" [ID], [日期], [部门单位], [车间班组], [用工性质], [姓名],  [粮贴], [洗理费], [水电气补贴], [三项补贴], [回贴],  [职称津贴], [特岗津贴], [行车工津贴], [卫生纸费],   [扣电视费], [扣治安费], [扣会费], [扣互助金], [扣丧葬费],  [岗位名称], [性别], [参加工作时间], [工号], [身份证号], [帐号], [老工号], [备注],部门单位备注,  [公积金号], [医疗保险号], [出生日期], [党政职别], [职称], [干工标识], [籍贯_出生地], [毕业时间文化程度] ");
            strSql.Append(" FROM GZ ");
            strSql.Append("  where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') and (车间班组 like '%'+@strWhere1+'%' or @strWhere1='') and (姓名 like '%'+@strWhere2+'%' or @strWhere2='') ");
            strSql.Append(" and  是否保护='否'  order by  id asc ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",部门单位),
                new SqlParameter("@strWhere1",车间班组),
                new SqlParameter("@strWhere2",姓名),
                

            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
        /// <summary>
        /// 初始化NULL值为0
        /// </summary>
        public int GZ_LG_Add(String 身份证号)
        {
            List<String> list = new List<string>();
            list.Add("update GZ set [技能工资]=0 where [技能工资] is Null  ");
            list.Add( "select  [日期], [部门单位], [车间班组], [姓名], [性别], [技能工资], [岗位工资], [绩效工资], [粮贴], [洗理费], [水电气补贴], [三项补贴], [回贴], [工龄津贴], [职称津贴], [特岗津贴], [行车工津贴], [卫生纸费], [车贴], [星级津贴], [津贴1], [津贴2], [津贴3], [津贴4], [津贴5], [五项津贴], [日班], [中班], [晚班], [大晚班], [中晚班费], [加班], [加班工资], [补发], [事假], [扣事假工资], [病假], [扣病假工资], [公假], [工伤], [产假], [其它扣款], [应发总额], [应发], [扣失业保险], [扣养老金], [扣医疗保险], [扣公积金], [扣电视费], [扣治安费], [扣会费], [扣互助金], [扣丧葬费], [扣款1], [扣款2], [扣款3], [扣款4], [扣款5], [五项扣款], [实发金额], [实发], [帐号], [技能工资级别], [岗位工资类别], [岗位名称], [参加工作时间], [身份证号], [用工性质], [备注], [部门单位备注], [工号], [老工号], [公积金号], [医疗保险号], [出生日期], [党政职别], [职称], [干工标识], [籍贯_出生地], [毕业时间文化程度], [人数], [是否保护], [行车工津贴2] into GZTmp from gz  where 身份证号='" + 身份证号 + "' ");

            list.Add( "insert into  GZ_LG ([日期], [部门单位], [车间班组], [姓名], [性别], [技能工资], [岗位工资], [绩效工资], [粮贴], [洗理费], [水电气补贴], [三项补贴], [回贴], [工龄津贴], [职称津贴], [特岗津贴], [行车工津贴], [卫生纸费], [车贴], [星级津贴], [津贴1], [津贴2], [津贴3], [津贴4], [津贴5], [五项津贴], [日班], [中班], [晚班], [大晚班], [中晚班费], [加班], [加班工资], [补发], [事假], [扣事假工资], [病假], [扣病假工资], [公假], [工伤], [产假], [其它扣款], [应发总额], [应发], [扣失业保险], [扣养老金], [扣医疗保险], [扣公积金], [扣电视费], [扣治安费], [扣会费], [扣互助金], [扣丧葬费], [扣款1], [扣款2], [扣款3], [扣款4], [扣款5], [五项扣款], [实发金额], [实发], [帐号], [技能工资级别], [岗位工资类别], [岗位名称], [参加工作时间], [身份证号], [用工性质], [备注], [部门单位备注], [工号], [老工号], [公积金号], [医疗保险号], [出生日期], [党政职别], [职称], [干工标识], [籍贯_出生地], [毕业时间文化程度], [人数], [是否保护], [行车工津贴2]) select   [日期], [部门单位], [车间班组], [姓名], [性别], [技能工资], [岗位工资], [绩效工资], [粮贴], [洗理费], [水电气补贴], [三项补贴], [回贴], [工龄津贴], [职称津贴], [特岗津贴], [行车工津贴], [卫生纸费], [车贴], [星级津贴], [津贴1], [津贴2], [津贴3], [津贴4], [津贴5], [五项津贴], [日班], [中班], [晚班], [大晚班], [中晚班费], [加班], [加班工资], [补发], [事假], [扣事假工资], [病假], [扣病假工资], [公假], [工伤], [产假], [其它扣款], [应发总额], [应发], [扣失业保险], [扣养老金], [扣医疗保险], [扣公积金], [扣电视费], [扣治安费], [扣会费], [扣互助金], [扣丧葬费], [扣款1], [扣款2], [扣款3], [扣款4], [扣款5], [五项扣款], [实发金额], [实发], [帐号], [技能工资级别], [岗位工资类别], [岗位名称], [参加工作时间], [身份证号], [用工性质], [备注], [部门单位备注], [工号], [老工号], [公积金号], [医疗保险号], [出生日期], [党政职别], [职称], [干工标识], [籍贯_出生地], [毕业时间文化程度], [人数], [是否保护], [行车工津贴2] from  GZTmp");
            list.Add("if  exists (select name from sysobjects where name='GZTmp')  drop table GZTmp ");
            
                
                return DbHelperSQL.ExecuteSqlTran(list);
            
        }
        /// <summary>
        /// 根据部门获得数据列表
        /// </summary>
        public DataSet GetListBM()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select distinct 部门单位 from GZ  where 是否保护='否' ");
            

            return DbHelperSQL.Query(strSql.ToString());

        }
        /// <summary>
        /// 根据部门获得非关键项表
        /// </summary>
        public DataSet searchKey(string 部门单位, String 车间班组, String 姓名)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");

           strSql.Append(" FROM GZ ");
            strSql.Append("  where 1=1 and (部门单位 like '%'+@strWhere+'%' or @strWhere='') and (车间班组 like '%'+@strWhere1+'%' or @strWhere1='') and (姓名 like '%'+@strWhere2+'%' or @strWhere2='') ");
            strSql.Append(" and 是否保护='否'   order by 姓名 asc ");

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere",部门单位),
                new SqlParameter("@strWhere1",车间班组),
                new SqlParameter("@strWhere2",姓名),
                

            };

            return DbHelperSQL.Query(strSql.ToString(), param);

        }
    }
}
